﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using Entities;
using System.Data.SqlClient;
using DAOExceptions;

namespace SQLDao
{
    public class SQL_User_DAO : IUserDAO
    {
        public User LoadUser(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add("@id", id);
            command.CommandText = "SELECT [id], [login], [password], [email], [last_quota]  FROM [Users] WHERE [id]=@id";
            User user=new User();
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        user.ID = (int)reader["id"];
                        user.Login = (string)reader["login"];
                        user.Password = (string)reader["password"];
                        user.Email = (string)reader["email"];
                        user.LastQuota = (int)reader["last_quota"];
                    }
                    else
                    {
                        return user;
                    }
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading user");
            }
            IRoleDAO rdao = new SQL_Role_DAO();
            user.Roles = rdao.GetRolesForUser(user.ID);

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            user.HostedFiles = hfdao.GetHostedFilesForUser(user.ID);

            user.State = EntityState.Unchanged;
            return user;
        }

        public bool IsValidAuth(User auth_user)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add("@login", auth_user.Login);
            command.Parameters.Add("@password", auth_user.Password);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [login]=@login AND [password]=@password";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking user auth params");
            }
        }

        public User LoadUser(string login)
        {
            return LoadUser(GetUserID(login));
        }

        public bool UserExists(int id)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add("@id", id);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [id]=@id";
            try
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while checking user existence");
            }
        }

        public bool UserExists(string login)
        {
            return (GetUserID(login) != -1);
        }

        public int GetUserID(string login)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add("@login", login);
            command.CommandText = "SELECT [id] FROM [Users] WHERE [login]=@login";
            try
            {
                var res = command.ExecuteScalar().ToString();
                int result = -1;
                int.TryParse(res, out result);
                return result;
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while loading user id");
            }
        }

        private void ModifyUser(User src)
        {
            var old_user = LoadUser(src.ID);
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@login", src.Login));
            command.Parameters.Add(new SqlParameter("@password", src.Password));
            command.Parameters.Add(new SqlParameter("@email", src.Email));
            command.Parameters.Add(new SqlParameter("@lastquota", src.LastQuota));
            command.CommandText = "UPDATE [Users] SET [login]=@login, [password]=@password, [email]=@email, [last_quota]=@lastquota WHERE [id]=@id";
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException)
            {
                throw new DAOException("Error occured while modifying user");
            }

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            foreach (var file_Item in old_user.HostedFiles)
            {
                hfdao.UnlinkHostedFile(file_Item);
            }
            foreach (var file_item in src.HostedFiles)
            {
                hfdao.LinkHostedFile(file_item, src.ID);
            }


            IRoleDAO rdao = new SQL_Role_DAO();
            foreach (var role_item in old_user.Roles)
            {
                rdao.RemoveRoleFromUser(role_item.ID, src.ID);
            }
            foreach (var role_item in src.Roles)
            {
                rdao.AddRoleToUser(role_item.ID, src.ID);
            }

        }

        private void RemoveUser(User src)
        {
            // Removing roles
            IRoleDAO rdao = new SQL_Role_DAO();
            var user_roles = rdao.GetRolesForUser(src.ID);
            foreach (var roleItem in user_roles)
            {
                rdao.RemoveRoleFromUser(roleItem.ID, src.ID);
            }

            // HACK: We cannot directly access FileDAO, so stored files will last on hdd until we found them not connected to HostedFiles DB and remove

            // Removing hosted files
            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            var user_files = hfdao.GetHostedFilesForUser(src.ID);
            foreach (var hfileItem in user_files)
            {
                hfdao.UnlinkHostedFile(hfileItem);
            }

            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add("@id", src.ID);
            command.CommandText = "DELETE FROM [Users] WHERE [id]=@id";
            command.ExecuteNonQuery();
            return;
        }

        private int InsertUser(User src)
        {
            SqlCommand command = new SqlCommand();
            command.Connection = SQL_DAO_Connection.Connection;
            command.Parameters.Add(new SqlParameter("@login", src.Login));
            command.Parameters.Add(new SqlParameter("@password", src.Password));
            command.Parameters.Add(new SqlParameter("@email", src.Email));
            command.Parameters.Add(new SqlParameter("@lastquota", src.LastQuota));
            command.CommandText = "INSERT INTO [Users] ([login], [password], [email], [last_quota]) VALUES(@login, @password, @email, @lastquota)";
            command.ExecuteNonQuery();
            command.Parameters.Clear();
            command.CommandText = "SELECT SCOPE_IDENTITY()";
            var result = command.ExecuteScalar().ToString();
            int res = -1;
            if (!int.TryParse(result, out res))
            {
                return -1;
            }

            IHostedFileDAO hfdao = new SQL_HostedFile_DAO();
            foreach (var file_Item in src.HostedFiles)
            {
                hfdao.LinkHostedFile(file_Item, res);
            }

            IRoleDAO rdao = new SQL_Role_DAO();
            foreach (var role_item in src.Roles)
            {
                rdao.AddRoleToUser(role_item.ID, src.ID);
            }

            return res;
        }

    }
}
